package org.adw.launcher2.ld.secret.db;

import java.util.LinkedList;
import java.util.List;
import org.adw.launcher2.ld.secret.dom.MySMS;
import org.adw.launcher2.ld.secret.dom.contact.ConversationSMS;
import org.adw.launcher2.ld.secret.ui.utils.Global;
import android.content.ContentResolver;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.provider.ContactsContract.Contacts;
import android.provider.ContactsContract.CommonDataKinds.Phone;
public class MySMSCache {
	public static String userTable = "CREATE TABLE IF NOT EXISTS my_sms_user" +
	"(id  INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE ,"+"user_name TEXT,"+ "phone_number TEXT NOT NULL  UNIQUE);";
	
	public static String smsTable = "CREATE TABLE IF NOT EXISTS " +
	"my_sms_data (id INTEGER, is_me INTEGER, value TEXT," +
	"FOREIGN KEY(id) REFERENCES my_sms_user(id) ON DELETE CASCADE);";
	// is_me = 1 ==> la tin nhan minh gui di, id = 0 la tin nhan nhan duoc
	
	public static String userTableCache = "CREATE TABLE IF NOT EXISTS my_sms_user_cache" +
	"(id  INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE ,"+"user_name TEXT,"+ "phone_number TEXT NOT NULL  UNIQUE);";
	
	private static String TABLE_MY_SMS_USER = "my_sms_user";
	private static String TABLE_MY_SMS_DATA = "my_sms_data";
	private static String TABLE_MY_SMS_CACHE = "my_sms_user_cache";
	private static MySMSCache instance = null;
	private SQLiteDatabase db;
	public static MySMSCache getInstance(DatabaseHelper dbHelper) {
		if (instance == null) {
			instance = new MySMSCache(dbHelper);
			
		}
		return instance;
	}

	private MySMSCache(DatabaseHelper dbHelper) {
		db = dbHelper.db;
		onCreateTable();
	}

	private void onCreateTable() {
		// enable foreign key
		db.execSQL(Global.enableForeignKey);
		db.execSQL(userTable);
		db.execSQL(smsTable);
	}

	public void insertUserToDb(ContentValues values) {// id, user_name, phone_number
		db.insert(TABLE_MY_SMS_USER, null, values);
	}
	
	public void insertSMSToDb(ContentValues values) {//id, is_me, value
		db.insert(TABLE_MY_SMS_DATA, null, values);
	}
	
	public void deleteUserFromDb(long id) {
		db.delete(TABLE_MY_SMS_USER, " id = ? ", new String[] { id + "" });
	}
	public List<MySMS> getUsersInfo(){
		List<MySMS> list = new LinkedList<MySMS>();
		Cursor cursor = db.rawQuery("SELECT * FROM my_sms_user ", null);
		while(cursor.moveToNext()){
			// chua lay thong tin ve Image
			String name = cursor.getString(cursor.getColumnIndex("user_name"));//tham khao ten cac cot trong Global.java
			String phone = cursor.getString(cursor.getColumnIndex("phone_number"));
			long id = cursor.getLong(cursor.getColumnIndex("id"));
			list.add(new MySMS(id, name,phone));
		}
		cursor.close();
		return list;
	}
	public void insertToCacheTable(ContentValues values) {// id, user_name, phone_number
		db.insert(TABLE_MY_SMS_CACHE, null, values);
	}
	public void backUpUser(final ContentResolver cr){
		if(Global.dbHelper.isTableExists(TABLE_MY_SMS_CACHE)) return ;
		
		db.execSQL(userTableCache);
		new DbBatchOperation(this.db) {
			
			@Override
			public void doBatchInsert() {
				Cursor cursor = cr.query(Contacts.CONTENT_URI, 
						new String[]{"_id", "display_name"}, null, null, null);
				Cursor phoneCursor = null;
				while (cursor.moveToNext()) {
					String userName = cursor.getString(cursor.getColumnIndex(Contacts.DISPLAY_NAME));//user name
					long userId = cursor.getLong(cursor.getColumnIndex(Contacts._ID));//id
					String phoneWhere = Phone.CONTACT_ID + " = ? ";
					String[] phoneWhereParams = new String[] { userId + "" };
					phoneCursor = cr.query(Phone.CONTENT_URI, new String[]{Phone.NUMBER}, phoneWhere,
							phoneWhereParams, null);
					String phoneValue = "";
					if (phoneCursor.moveToFirst()) {
						phoneValue = phoneCursor.getString(phoneCursor
								.getColumnIndex(Phone.NUMBER));
					}
					ContentValues values = new ContentValues();
					values.put("id", userId);
					values.put("user_name", userName);
					values.put("phone_number", phoneValue);
					insertToCacheTable(values);
				}
				phoneCursor.close();
				cursor.close();
			}
		};
	}
	public List<MySMS> getUsersInfoFromDB(){
		List<MySMS> list = new LinkedList<MySMS>();
		Cursor cursor = db.rawQuery("SELECT * FROM my_sms_user_cache ", null);
		while(cursor.moveToNext()){
			// chua lay thong tin ve Image
			String name = cursor.getString(cursor.getColumnIndex("user_name"));//tham khao ten cac cot trong Global.java
			String phone = cursor.getString(cursor.getColumnIndex("phone_number"));
			long id = cursor.getLong(cursor.getColumnIndex("id"));
			list.add(new MySMS(id, name,phone));
		}
		cursor.close();
		return list;
	}
	public long isUserExist(String phoneNumber){
		Cursor cursor = db.rawQuery("SELECT * FROM my_sms_user ", null);
		while(cursor.moveToNext()){
			// chua lay thong tin ve Image
			String phone = cursor.getString(cursor.getColumnIndex("phone_number"));
			if(phone.equals(phoneNumber)){
				long id = cursor.getLong(cursor.getColumnIndex("id"));
				return id;
			}
		}
		cursor.close();
		return -1;
	}
	public List<ConversationSMS> loadConversationSMS(long id){
		List<ConversationSMS>list = new LinkedList<ConversationSMS>();
		String userName = "";
		String phoneNumber = "";
		Cursor cursor = db.rawQuery("SELECT * FROM my_sms_user WHERE id = " + id, null);
		if(cursor.moveToFirst()){
			userName = cursor.getString(cursor.getColumnIndex("phone_number"));
			phoneNumber = cursor.getString(cursor.getColumnIndex("user_name"));
		}
		cursor = db.rawQuery("SELECT * FROM my_sms_data WHERE id = " + id, null);
		while(cursor.moveToNext()){
			int isMe = cursor.getInt(cursor.getColumnIndex("is_me"));
			String smsValue = cursor.getString(cursor.getColumnIndex("value"));
			list.add(new ConversationSMS(id, userName, smsValue, phoneNumber, isMe));
		}
		cursor.close();
		return list;
	}
	
	


}
